{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "pd.set_option('precision', 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# DataFrame合并和变形\n",
    "* 合并\n",
    "* 变形"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker        date  open\n",
       "0  GOOGLE  2020-01-01  1100\n",
       "1  GOOGLE  2020-01-02  1200\n",
       "2  GOOGLE  2020-01-03  1300\n",
       "3  GOOGLE  2020-01-04  1400\n",
       "4   AAPLE  2020-01-01   161\n",
       "5   AAPLE  2020-01-02   281\n",
       "6   AAPLE  2020-01-03   455\n",
       "7   AAPLE  2020-01-04   105"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df=pd.DataFrame({\n",
    "    'ticker':['GOOGLE']*4+['AAPLE']*4,\n",
    "    'date':['2020-01-01','2020-01-02','2020-01-03','2020-01-04' ,'2020-01-01','2020-01-02','2020-01-03','2020-01-04'],\n",
    "    'open':[1100,1200,1300,1400,161,281,455,105]\n",
    "})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>301</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  price\n",
       "0  2020-01-01    278\n",
       "1  2020-01-02    280\n",
       "2  2020-01-03    301"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sp=pd.DataFrame({\n",
    "        'date':['2020-01-01','2020-01-02','2020-01-03'],\n",
    "    'price':[278,280,301]\n",
    "\n",
    "})\n",
    "sp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1100</td>\n",
       "      <td>278.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>1200</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1300</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>1400</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>161</td>\n",
       "      <td>278.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>281</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>455</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>105</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker        date  open  price\n",
       "0  GOOGLE  2020-01-01  1100  278.0\n",
       "1  GOOGLE  2020-01-02  1200  280.0\n",
       "2  GOOGLE  2020-01-03  1300  301.0\n",
       "3  GOOGLE  2020-01-04  1400    NaN\n",
       "4   AAPLE  2020-01-01   161  278.0\n",
       "5   AAPLE  2020-01-02   281  280.0\n",
       "6   AAPLE  2020-01-03   455  301.0\n",
       "7   AAPLE  2020-01-04   105    NaN"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df,sp,on='date',how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join\n",
    "#### 和merge基本一样，但是对index进行join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>date_x</th>\n",
       "      <th>open</th>\n",
       "      <th>date_y</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1100</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>278.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>1200</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1300</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>1400</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>161</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>281</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>455</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>105</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker      date_x  open      date_y  price\n",
       "0  GOOGLE  2020-01-01  1100  2020-01-01  278.0\n",
       "1  GOOGLE  2020-01-02  1200  2020-01-02  280.0\n",
       "2  GOOGLE  2020-01-03  1300  2020-01-03  301.0\n",
       "3  GOOGLE  2020-01-04  1400         NaN    NaN\n",
       "4   AAPLE  2020-01-01   161         NaN    NaN\n",
       "5   AAPLE  2020-01-02   281         NaN    NaN\n",
       "6   AAPLE  2020-01-03   455         NaN    NaN\n",
       "7   AAPLE  2020-01-04   105         NaN    NaN"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.join(sp,lsuffix='_x', rsuffix='_y')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>open</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1100</td>\n",
       "      <td>278.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>161</td>\n",
       "      <td>278.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1200</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>281</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1300</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>455</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1400</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>105</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            ticker  open  price\n",
       "date                           \n",
       "2020-01-01  GOOGLE  1100  278.0\n",
       "2020-01-01   AAPLE   161  278.0\n",
       "2020-01-02  GOOGLE  1200  280.0\n",
       "2020-01-02   AAPLE   281  280.0\n",
       "2020-01-03  GOOGLE  1300  301.0\n",
       "2020-01-03   AAPLE   455  301.0\n",
       "2020-01-04  GOOGLE  1400    NaN\n",
       "2020-01-04   AAPLE   105    NaN"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.set_index('date').join(sp.set_index('date'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1100</td>\n",
       "      <td>278.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>1200</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1300</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>1400</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>161</td>\n",
       "      <td>278.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>281</td>\n",
       "      <td>280.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>455</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>105</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker        date  open  price\n",
       "0  GOOGLE  2020-01-01  1100  278.0\n",
       "1  GOOGLE  2020-01-02  1200  280.0\n",
       "2  GOOGLE  2020-01-03  1300  301.0\n",
       "3  GOOGLE  2020-01-04  1400    NaN\n",
       "4   AAPLE  2020-01-01   161  278.0\n",
       "5   AAPLE  2020-01-02   281  280.0\n",
       "6   AAPLE  2020-01-03   455  301.0\n",
       "7   AAPLE  2020-01-04   105    NaN"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(df,sp,on='date',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## 变形\n",
    "* Pivot\n",
    "* Crosstab\n",
    "* Melt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker        date  open\n",
       "0  GOOGLE  2020-01-01  1100\n",
       "1  GOOGLE  2020-01-02  1200\n",
       "2  GOOGLE  2020-01-03  1300\n",
       "3  GOOGLE  2020-01-04  1400\n",
       "4   AAPLE  2020-01-01   161\n",
       "5   AAPLE  2020-01-02   281\n",
       "6   AAPLE  2020-01-03   455\n",
       "7   AAPLE  2020-01-04   105"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>161</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>281</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>455</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>105</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE\n",
       "date                     \n",
       "2020-01-01    161    1100\n",
       "2020-01-02    281    1200\n",
       "2020-01-03    455    1300\n",
       "2020-01-04    105    1400"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='date',columns='ticker',values='open')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>161</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>281</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>455</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>105</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE\n",
       "date                     \n",
       "2020-01-01    161    1100\n",
       "2020-01-02    281    1200\n",
       "2020-01-03    455    1300\n",
       "2020-01-04    105    1400"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot(index='date',columns='ticker',values='open',data=df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>161</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>281</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>455</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>105</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE\n",
       "date                     \n",
       "2020-01-01    161    1100\n",
       "2020-01-02    281    1200\n",
       "2020-01-03    455    1300\n",
       "2020-01-04    105    1400"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index='date',columns='ticker',values='open')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>161</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>281</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>455</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>105</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE\n",
       "date                     \n",
       "2020-01-01    161    1100\n",
       "2020-01-02    281    1200\n",
       "2020-01-03    455    1300\n",
       "2020-01-04    105    1400"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(index='date',columns='ticker',values='open',data=df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>161</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>281</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>455</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>105</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE\n",
       "date                     \n",
       "2020-01-01    161    1100\n",
       "2020-01-02    281    1200\n",
       "2020-01-03    455    1300\n",
       "2020-01-04    105    1400"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index='date',columns='ticker',values='open',aggfunc='mean')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.append(df,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>322</td>\n",
       "      <td>2200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>562</td>\n",
       "      <td>2400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>910</td>\n",
       "      <td>2600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>210</td>\n",
       "      <td>2800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE\n",
       "date                     \n",
       "2020-01-01    322    2200\n",
       "2020-01-02    562    2400\n",
       "2020-01-03    910    2600\n",
       "2020-01-04    210    2800"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index='date',columns='ticker',values='open',aggfunc='sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>322</td>\n",
       "      <td>2200</td>\n",
       "      <td>2522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>562</td>\n",
       "      <td>2400</td>\n",
       "      <td>2962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>910</td>\n",
       "      <td>2600</td>\n",
       "      <td>3510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>210</td>\n",
       "      <td>2800</td>\n",
       "      <td>3010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>2004</td>\n",
       "      <td>10000</td>\n",
       "      <td>12004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE    All\n",
       "date                            \n",
       "2020-01-01    322    2200   2522\n",
       "2020-01-02    562    2400   2962\n",
       "2020-01-03    910    2600   3510\n",
       "2020-01-04    210    2800   3010\n",
       "All          2004   10000  12004"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index='date',columns='ticker',values='open',aggfunc='sum',margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Crosstab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>322</td>\n",
       "      <td>2200</td>\n",
       "      <td>2522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>562</td>\n",
       "      <td>2400</td>\n",
       "      <td>2962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>910</td>\n",
       "      <td>2600</td>\n",
       "      <td>3510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>210</td>\n",
       "      <td>2800</td>\n",
       "      <td>3010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>2004</td>\n",
       "      <td>10000</td>\n",
       "      <td>12004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE    All\n",
       "date                            \n",
       "2020-01-01    322    2200   2522\n",
       "2020-01-02    562    2400   2962\n",
       "2020-01-03    910    2600   3510\n",
       "2020-01-04    210    2800   3010\n",
       "All          2004   10000  12004"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.crosstab(index=df['date'],columns=df['ticker'],values=df['open'],aggfunc='sum',margins=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'crosstab'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-81-774e4ea9ff1e>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcrosstab\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mindex\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'date'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'ticker'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'open'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0maggfunc\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m'sum'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32m~\\anaconda3\\envs\\base (root)\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m__getattr__\u001b[1;34m(self, name)\u001b[0m\n\u001b[0;32m   5272\u001b[0m             \u001b[1;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_info_axis\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_can_hold_identifiers_and_holds_name\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5273\u001b[0m                 \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5274\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   5275\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5276\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__setattr__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m->\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'crosstab'"
     ]
    }
   ],
   "source": [
    "df.crosstab(index='date',columns='ticker',values='open',aggfunc='sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>161</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>281</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>455</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>105</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "ticker      AAPLE  GOOGLE\n",
       "date                     \n",
       "2020-01-01    161    1100\n",
       "2020-01-02    281    1200\n",
       "2020-01-03    455    1300\n",
       "2020-01-04    105    1400"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt=df.pivot_table(index='date',columns='ticker',values='open',aggfunc='sum')\n",
    "pt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  ticker  value\n",
       "0  2020-01-01   AAPLE    161\n",
       "1  2020-01-02   AAPLE    281\n",
       "2  2020-01-03   AAPLE    455\n",
       "3  2020-01-04   AAPLE    105\n",
       "4  2020-01-01  GOOGLE   1100\n",
       "5  2020-01-02  GOOGLE   1200\n",
       "6  2020-01-03  GOOGLE   1300\n",
       "7  2020-01-04  GOOGLE   1400"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt.reset_index().melt(id_vars='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['2020-01-01', '2020-01-02', '2020-01-03', '2020-01-04'], dtype='object', name='date')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['AAPLE', 'GOOGLE'], dtype='object', name='ticker')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 课后练习"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'foo': ['one', 'one', 'one', 'two', 'two',\n",
    "                           'two'],\n",
    "                   'bar': ['A', 'B', 'C', 'A', 'B', 'C'],\n",
    "                   'baz': [1, 2, 3, 4, 5, 6],\n",
    "                   'zoo': ['x', 'y', 'z', 'q', 'w', 't']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['AAPLE', 'GOOGLE'], dtype='object', name='ticker')"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>2800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ticker        date  open\n",
       "0   AAPLE  2020-01-01   322\n",
       "1   AAPLE  2020-01-02   562\n",
       "2   AAPLE  2020-01-03   910\n",
       "3   AAPLE  2020-01-04   210\n",
       "4  GOOGLE  2020-01-01  2200\n",
       "5  GOOGLE  2020-01-02  2400\n",
       "6  GOOGLE  2020-01-03  2600\n",
       "7  GOOGLE  2020-01-04  2800"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(['ticker','date']).sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>ticker</th>\n",
       "      <th>open</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2800</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  ticker  open\n",
       "0  2020-01-01   AAPLE   322\n",
       "1  2020-01-02   AAPLE   562\n",
       "2  2020-01-03   AAPLE   910\n",
       "3  2020-01-04   AAPLE   210\n",
       "4  2020-01-01  GOOGLE  2200\n",
       "5  2020-01-02  GOOGLE  2400\n",
       "6  2020-01-03  GOOGLE  2600\n",
       "7  2020-01-04  GOOGLE  2800"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt.reset_index().melt(id_vars='date',value_name='open')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>date</th>\n",
       "      <th>open</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>1200</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1300</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>1400</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>161</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>281</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>455</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>105</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>1200</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>1300</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>1400</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>161</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>281</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>455</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>AAPLE</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>105</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ticker        date  open  price\n",
       "0   GOOGLE  2020-01-01  1100    100\n",
       "1   GOOGLE  2020-01-02  1200    100\n",
       "2   GOOGLE  2020-01-03  1300    100\n",
       "3   GOOGLE  2020-01-04  1400    100\n",
       "4    AAPLE  2020-01-01   161    100\n",
       "5    AAPLE  2020-01-02   281    100\n",
       "6    AAPLE  2020-01-03   455    100\n",
       "7    AAPLE  2020-01-04   105    100\n",
       "8   GOOGLE  2020-01-01  1100    100\n",
       "9   GOOGLE  2020-01-02  1200    100\n",
       "10  GOOGLE  2020-01-03  1300    100\n",
       "11  GOOGLE  2020-01-04  1400    100\n",
       "12   AAPLE  2020-01-01   161    100\n",
       "13   AAPLE  2020-01-02   281    100\n",
       "14   AAPLE  2020-01-03   455    100\n",
       "15   AAPLE  2020-01-04   105    100"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['price']=100\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">open</th>\n",
       "      <th colspan=\"2\" halign=\"left\">price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>161</td>\n",
       "      <td>1100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>281</td>\n",
       "      <td>1200</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>455</td>\n",
       "      <td>1300</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>105</td>\n",
       "      <td>1400</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            open        price       \n",
       "ticker     AAPLE GOOGLE AAPLE GOOGLE\n",
       "date                                \n",
       "2020-01-01   161   1100   100    100\n",
       "2020-01-02   281   1200   100    100\n",
       "2020-01-03   455   1300   100    100\n",
       "2020-01-04   105   1400   100    100"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt=df.pivot_table(index='date',columns='ticker',values=['open','price'])\n",
    "pt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>label</th>\n",
       "      <th>ticker</th>\n",
       "      <th>open</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>open</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>open</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>open</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>open</td>\n",
       "      <td>AAPLE</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>open</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>open</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>open</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>open</td>\n",
       "      <td>GOOGLE</td>\n",
       "      <td>1400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date label  ticker  open\n",
       "0  2020-01-01  open   AAPLE   161\n",
       "1  2020-01-02  open   AAPLE   281\n",
       "2  2020-01-03  open   AAPLE   455\n",
       "3  2020-01-04  open   AAPLE   105\n",
       "4  2020-01-01  open  GOOGLE  1100\n",
       "5  2020-01-02  open  GOOGLE  1200\n",
       "6  2020-01-03  open  GOOGLE  1300\n",
       "7  2020-01-04  open  GOOGLE  1400"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pt.reset_index().melt(id_vars='date',value_vars='open',var_name=['label','ticker'],value_name='open')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">open</th>\n",
       "      <th colspan=\"2\" halign=\"left\">price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ticker</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "      <th>AAPLE</th>\n",
       "      <th>GOOGLE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2020-01-01</th>\n",
       "      <td>161</td>\n",
       "      <td>1100</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-02</th>\n",
       "      <td>281</td>\n",
       "      <td>1200</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-03</th>\n",
       "      <td>455</td>\n",
       "      <td>1300</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-01-04</th>\n",
       "      <td>105</td>\n",
       "      <td>1400</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            open        price       \n",
       "ticker     AAPLE GOOGLE AAPLE GOOGLE\n",
       "date                                \n",
       "2020-01-01   161   1100   100    100\n",
       "2020-01-02   281   1200   100    100\n",
       "2020-01-03   455   1300   100    100\n",
       "2020-01-04   105   1400   100    100"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:**\n",
    "\n",
    "通过pivot转变数据格式，以bar作为index，foo作为column，zoo 为value\n",
    "\n",
    "通过melt把数据格式恢复"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "351px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
